package com.zhanghe.mall.pojo.repository;

import com.zhanghe.mall.pojo.CommodityClassificationTree;
import org.hibernate.annotations.SQLInsert;
import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.data.jpa.repository.Modifying;
import org.springframework.data.jpa.repository.Query;
import org.springframework.data.repository.query.Param;

import java.util.List;

public interface CommodityClassficationTreeRepository extends JpaRepository<CommodityClassificationTree,Long> {

    /**
     * 查询 ancestor 的 距离为 distance 的子节点
     * @param ancestor_id
     * @param distance
     * @return
     */
    List<CommodityClassificationTree> findByAncestor_IdAndDistance(Long ancestor_id,Long distance);
    /**
     * 查询 ancestor 的 距离大于 distance 的子节点
     * @param ancestor_id
     * @param distance
     * @return
     */
    List<CommodityClassificationTree> findByAncestor_IdAndDistanceGreaterThan(Long ancestor_id,Long distance);

    /**
     * 复制父节点的关系
     * @param descendant
     * @param parentDescendant
     * @return
     */
    @Modifying(flushAutomatically = true)
    @Query(value = "insert into commodity_classification_tree(ancestor,descendant,distance,add_time,edit_time,is_delete) " +
            "(select ancestor,:descendant,distance+1,NOW(),NOW(),0 from commodity_classification_tree where descendant=:parentDescendant)"
    ,nativeQuery = true)
    Integer insertClassficationTreeRepository(@Param("descendant") Long descendant,@Param("parentDescendant") Long parentDescendant);

    /**
     * 添加自己的关系
     * @param ancestor
     * @param descendant
     * @param distance
     * @return
     */
    @Modifying
    @Query(value = "insert into commodity_classification_tree(ancestor,descendant,distance,add_time,edit_time,is_delete) values " +
            "(:ancestor,:descendant,:distance,NOW(),NOW(),0)"
            ,nativeQuery = true)
    Integer insertSelf(@Param("ancestor") Long ancestor,@Param("descendant") Long descendant,@Param("distance") Long distance);

    /**
     * 查询根节点
     * @return
     */
    @Query(value = "select * from commodity_classification_tree t where not exists" +
            "(select * from commodity_classification_tree s where s.descendant=t.descendant and s.ancestor!=t.ancestor)" ,nativeQuery = true)
    List<CommodityClassificationTree> findRoot();

    /**
     * 查询该节点的所有下级节点
     * @param ancestorId
     * @return
     */
    List<CommodityClassificationTree> findByAncestor_Id(Long ancestorId);

    /**
     * 查询该节点的所有父级结点
     * @param descendantId
     * @return
     */
    List<CommodityClassificationTree> findByDescendant_Id(Long descendantId);
}
